<!DOCTYPE html>



  


<html class="theme-next gemini use-motion" lang="zh-Hans">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>
<meta name="theme-color" content="#222">









<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />
















  
  
  <link href="/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />







<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=5.1.4" rel="stylesheet" type="text/css" />


  <link rel="apple-touch-icon" sizes="180x180" href="/images/ldx.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="32x32" href="/images/ldx.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="16x16" href="/images/ldx.png?v=5.1.4">


  <link rel="mask-icon" href="/images/ldx1.svg?v=5.1.4" color="#222">





  <meta name="keywords" content="Oracle,SQL优化," />




  


  <link rel="alternate" href="/atom.xml" title="Ysshao'Blog" type="application/atom+xml" />






<meta name="description" content="SQL性能优化案例篇学会辨别虚假信息在日常生活中，我们经常遇到有人说exits比in快，count(1)执行比count(*)快，但事实是真的这样吗？我们自己去进行过实践吗？ in与exist之争 首先创建测试表： 1234drop table emp purge;drop table dept purge;create table emp as select * from scott.emp;c">
<meta property="og:type" content="article">
<meta property="og:title" content="SQL性能优化案例">
<meta property="og:url" content="https://ysshao.cn/Oracle/SQL/Oracle_SQL_optimization/index.html">
<meta property="og:site_name" content="Ysshao&#39;Blog">
<meta property="og:description" content="SQL性能优化案例篇学会辨别虚假信息在日常生活中，我们经常遇到有人说exits比in快，count(1)执行比count(*)快，但事实是真的这样吗？我们自己去进行过实践吗？ in与exist之争 首先创建测试表： 1234drop table emp purge;drop table dept purge;create table emp as select * from scott.emp;c">
<meta property="og:image" content="https://ysshao.cn/Oracle/SQL/Oracle_SQL_optimization/sql.png">
<meta property="article:published_time" content="2020-05-01T02:50:30.000Z">
<meta property="article:modified_time" content="2020-05-08T10:38:29.945Z">
<meta property="article:author" content="ysshao">
<meta property="article:tag" content="Oracle">
<meta property="article:tag" content="SQL优化">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://ysshao.cn/Oracle/SQL/Oracle_SQL_optimization/sql.png">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Gemini',
    version: '5.1.4',
    sidebar: {"position":"left","display":"post","offset":12,"b2t":false,"scrollpercent":true,"onmobile":false},
    fancybox: true,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    duoshuo: {
      userId: '0',
      author: '博主'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="https://ysshao.cn/Oracle/SQL/Oracle_SQL_optimization/"/>





  <title>SQL性能优化案例 | Ysshao'Blog</title>
  





  <script type="text/javascript">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "https://hm.baidu.com/hm.js?50872c355dd0a87c31d032a47d741d41";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>




<meta name="generator" content="Hexo 4.2.0"></head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>
    <a href="https://ysshao.cn/Notes/" class="github-corner" aria-label="View source on GitHub"><svg width="80" height="80" viewBox="0 0 250 250" style="fill:#151513; color:#fff; position: absolute; top: 0; border: 0; right: 0;" aria-hidden="true"><path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path><path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"></path></svg></a><style>.github-corner:hover .octo-arm{animation:octocat-wave 560ms ease-in-out}@keyframes octocat-wave{0%,100%{transform:rotate(0)}20%,60%{transform:rotate(-25deg)}40%,80%{transform:rotate(10deg)}}@media (max-width:500px){.github-corner:hover .octo-arm{animation:none}.github-corner .octo-arm{animation:octocat-wave 560ms ease-in-out}}</style>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/"  class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">Ysshao'Blog</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle">人生太苦,多加点糖。</p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            标签
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/categories/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-th"></i> <br />
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br />
            
            关于
          </a>
        </li>
      

      
        <li class="menu-item menu-item-search">
          
            <a href="javascript:;" class="popup-trigger">
          
            
              <i class="menu-item-icon fa fa-search fa-fw"></i> <br />
            
            搜索
          </a>
        </li>
      
    </ul>
  

  
    <div class="site-search">
      
  <div class="popup search-popup local-search-popup">
  <div class="local-search-header clearfix">
    <span class="search-icon">
      <i class="fa fa-search"></i>
    </span>
    <span class="popup-btn-close">
      <i class="fa fa-times-circle"></i>
    </span>
    <div class="local-search-input-wrapper">
      <input autocomplete="off"
             placeholder="搜索..." spellcheck="false"
             type="text" id="local-search-input">
    </div>
  </div>
  <div id="local-search-result"></div>
</div>



    </div>
  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://ysshao.cn/Oracle/SQL/Oracle_SQL_optimization/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="ysshao">
      <meta itemprop="description" content="">
      <meta itemprop="image" content="/images/allen.jpg">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Ysshao'Blog">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">SQL性能优化案例</h1>
        

        <div class="post-meta">
          <span class="post-time">
          
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2020-05-01T10:50:30+08:00">
                2020-05-01
              </time>
            

            

            
          </span>

          
            <span class="post-category" >
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">分类于</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/Oracle/" itemprop="url" rel="index">
                    <span itemprop="name">Oracle</span>
                  </a>
                </span>

                
                
                  ， 
                
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/Oracle/SQL/" itemprop="url" rel="index">
                    <span itemprop="name">SQL</span>
                  </a>
                </span>

                
                
              
            </span>
          

          

          
          
             <span id="/Oracle/SQL/Oracle_SQL_optimization/" class="leancloud_visitors" data-flag-title="SQL性能优化案例">
               <span class="post-meta-divider">|</span>
               <span class="post-meta-item-icon">
                 <i class="fa fa-eye"></i>
               </span>
               
                 <span class="post-meta-item-text">阅读次数&#58;</span>
               
                 <span class="leancloud-visitors-count"></span>
             </span>
          

          

          
            <div class="post-wordcount">
              
                
                <span class="post-meta-item-icon">
                  <i class="fa fa-file-word-o"></i>
                </span>
                
                  <span class="post-meta-item-text">字数统计&#58;</span>
                
                <span title="字数统计">
                  5.1k
                </span>
              

              
                <span class="post-meta-divider">|</span>
              

              
                <span class="post-meta-item-icon">
                  <i class="fa fa-clock-o"></i>
                </span>
                
                  <span class="post-meta-item-text">阅读时长 &asymp;</span>
                
                <span title="阅读时长">
                  23
                </span>
              
            </div>
          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <h1 id="SQL性能优化案例篇"><a href="#SQL性能优化案例篇" class="headerlink" title="SQL性能优化案例篇"></a>SQL性能优化案例篇</h1><h2 id="学会辨别虚假信息"><a href="#学会辨别虚假信息" class="headerlink" title="学会辨别虚假信息"></a>学会辨别虚假信息</h2><p>在日常生活中，我们经常遇到有人说exits比in快，count(1)执行比count(*)快，但事实是真的这样吗？我们自己去进行过实践吗？</p>
<h3 id="in与exist之争"><a href="#in与exist之争" class="headerlink" title="in与exist之争"></a>in与exist之争</h3><p> 首先创建测试表：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> emp <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> dept <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> emp <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> scott.emp;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> dept <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> scott.dept;</span><br></pre></td></tr></table></figure>

<p> 在Oracle 11g中执行并查看执行计划：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> dept <span class="keyword">where</span> deptno <span class="keyword">NOT</span> <span class="keyword">IN</span> ( <span class="keyword">select</span> deptno <span class="keyword">from</span> emp ) ;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> dept <span class="keyword">where</span> <span class="keyword">not</span> <span class="keyword">exists</span> ( <span class="keyword">select</span> deptno <span class="keyword">from</span> emp <span class="keyword">where</span>     emp.deptno=dept.deptno) ;</span><br></pre></td></tr></table></figure>

<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set autotrace traceonly;</span><br><span class="line">SQL&gt; select * from dept where deptno NOT IN ( select deptno from emp ) ;</span><br></pre></td></tr></table></figure>

<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; set autotrace traceonly</span><br><span class="line">SQL&gt; select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;</span><br></pre></td></tr></table></figure>

<p>我们发现两个语句的效率是一样的，查看执行计划也是一样的。原来oracle在11g中已经做了优化，所以in和exist的效率是一样的。</p>
<p>由此我们可以得出结论，在11g中，使用in和exist的效率是一样的，因为他们走的都是比较高效的ANTI算法。而网上所说的in慢于exist是在10g的时候会出现的情况，因为10g的时候在不指定列为非空的情况下，in走的是filter的算法，现对于anti半连接算法比较低效，但是在10g当中sql语句指定不为空，也可走anti算法。比如：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> dept <span class="keyword">where</span> deptno <span class="keyword">NOT</span> <span class="keyword">IN</span> ( <span class="keyword">select</span> deptno <span class="keyword">from</span> emp <span class="keyword">where</span> deptno <span class="keyword">is</span> <span class="keyword">not</span> <span class="literal">null</span>) <span class="keyword">and</span> deptno <span class="keyword">is</span> <span class="keyword">not</span> <span class="literal">null</span>;</span><br></pre></td></tr></table></figure>

<h3 id="count谁最快"><a href="#count谁最快" class="headerlink" title="count谁最快"></a>count谁最快</h3><p> 首先创建测试表：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> <span class="keyword">test</span> <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="keyword">test</span> <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="keyword">update</span> <span class="keyword">test</span> <span class="keyword">set</span> object_id =<span class="keyword">rownum</span> ;</span><br><span class="line"><span class="keyword">commit</span>;</span><br></pre></td></tr></table></figure>

<p>  在无索引的情况下，比较count(*)与count(object_id)</p>
<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--查看执行时间</span></span><br><span class="line">SQL&gt; set timing on </span><br><span class="line">SQL&gt; set autotrace traceonly; </span><br><span class="line">SQL&gt; select count(*) from test;</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select count(object_id) from test;</span><br></pre></td></tr></table></figure>

<p>我们在列object_id上创建索引，再次比较count(*)与count(object_id)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_object_id <span class="keyword">on</span> <span class="keyword">test</span>(object_id);</span><br></pre></td></tr></table></figure>

<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select count(*) from test;</span><br></pre></td></tr></table></figure>

<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select count(object_id) from test;</span><br></pre></td></tr></table></figure>

<p>发现count(object_id)的速度明显比count(*)高出一大截，难道是因为count(object_id)能用到索引，所以效率才提高了很多？</p>
<p>我们再修改下object_id的列属性，再次比较count(*)与count(object_id)</p>
<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select count(*) from test;</span><br></pre></td></tr></table></figure>

<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select count(object_id) from test;</span><br></pre></td></tr></table></figure>

<p>结论：</p>
<p>1）对于上述实验来说，在一个无任何索引的表中count(*)与count(object_id)的性能是一样的；</p>
<p>2）在某一列有索引但此列可为空的前提下，count(*)比count(object_id)慢，因为count(object_id)用到了索引；</p>
<p>3）在某一列有索引但此列不为空的前提下count(*)与count(object_id)的性能是一样的；</p>
<p>另在没有索引的情况下，对不同的列做实验发现：</p>
<img src="/Oracle/SQL/Oracle_SQL_optimization/sql.png" alt="sql.ping" style="zoom:80%;">

<p>对于oracle优化器来说，count不同的列，统计的时间是不一样的，大致趋势是列越靠后，访问的开销越大，列的偏移量决定访问的性能。而count(*)的开销与偏移量无关。</p>
<p>因此，在某些场合count(*)反而是最快的。</p>
<h3 id="表的条件顺序"><a href="#表的条件顺序" class="headerlink" title="表的条件顺序"></a>表的条件顺序</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> t1 <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> t2 <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t1 <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t2 <span class="keyword">as</span> <span class="keyword">select</span> <span class="keyword">rownum</span> <span class="keyword">id</span> ,dbms_random.string(<span class="string">'b'</span>, <span class="number">50</span>) n ,data_object_id data_id <span class="keyword">from</span> dba_objects <span class="keyword">where</span> <span class="keyword">rownum</span>&lt;=<span class="number">10000</span>;</span><br><span class="line"><span class="keyword">set</span> autotrace traceonly</span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="keyword">set</span> timing <span class="keyword">on</span></span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+rule*/</span> * <span class="keyword">from</span> t1,t2 <span class="keyword">where</span> t1.object_id=<span class="number">29</span> <span class="keyword">and</span> t2.data_id&gt;<span class="number">8</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+rule*/</span> * <span class="keyword">from</span> t1,t2 <span class="keyword">where</span> t2.data_id&gt;<span class="number">8</span> <span class="keyword">and</span> t1.object_id=<span class="number">29</span> ;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">--加个关联条件看看，看看</span></span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+rule*/</span> * <span class="keyword">from</span> t1,t2 <span class="keyword">where</span> t1.object_id=t2.id <span class="keyword">and</span> t1.object_id=<span class="number">29</span> <span class="keyword">and</span> t2.data_id&gt;<span class="number">8</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+rule*/</span> * <span class="keyword">from</span> t1,t2 <span class="keyword">where</span> t1.object_id=t2.id <span class="keyword">and</span> t2.data_id&gt;<span class="number">8</span> <span class="keyword">and</span> t1.object_id=<span class="number">29</span> ;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>结论：通过上述实验发现，无论有过滤条件的WHERE放在后面还是放在前面，效果是一样的。因此，关于有过滤条件的WHERE放在后面的说法是错误的。</p>
</blockquote>
<h3 id="表的连接顺序"><a href="#表的连接顺序" class="headerlink" title="表的连接顺序"></a>表的连接顺序</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--来来来，做一个试验，看看SQL写法中，表的连接顺序是否很重要</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> tab_big;</span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> tab_small;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> tab_big  <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> tab_small  <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects <span class="keyword">where</span> <span class="keyword">rownum</span>&lt;=<span class="number">100</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">set</span> autotrace traceonly</span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="keyword">set</span> timing <span class="keyword">on</span> </span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> tab_big,tab_small   ;  </span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> tab_small,tab_big   ;</span><br><span class="line"><span class="comment">---奇怪，以上实验发现性能是一样的，咋回事呢，看来真是谣言啊，这真是恶意传谣吗？</span></span><br><span class="line"></span><br><span class="line"><span class="comment">---大家看看下面的语句，比较一下性能。</span></span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+rule*/</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> tab_big,tab_small ;  </span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+rule*/</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> tab_small,tab_big ;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>结论：原来表连接顺序的说法早就过时了，在基于规则的时代，小表在后，大表在前确实性能高于小表在前大表在后，现在oracle是基于代价的。性能是一样的。</p>
</blockquote>
<h2 id="索引优化的优劣"><a href="#索引优化的优劣" class="headerlink" title="索引优化的优劣"></a>索引优化的优劣</h2><h3 id="索引的优势"><a href="#索引的优势" class="headerlink" title="索引的优势"></a>索引的优势</h3><h4 id="索引优化之回表"><a href="#索引优化之回表" class="headerlink" title="索引优化之回表"></a>索引优化之回表</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--索引回表读（TABLE ACCESS BY INDEX ROWID）的例子</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> t <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="comment">--只建一个主键索引，查询所有字段*</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx1_object_id <span class="keyword">on</span> t(object_id);</span><br><span class="line"></span><br><span class="line"><span class="comment">--试验1</span></span><br><span class="line"><span class="keyword">set</span> autotrace traceonly</span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="keyword">set</span> timing <span class="keyword">on</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t <span class="keyword">where</span> object_id&lt;=<span class="number">5</span>;</span><br><span class="line"><span class="comment">--试验2</span></span><br><span class="line"><span class="comment">--比较消除TABLE ACCESS BY INDEX ROWID回表后的性能，将select * from改为select object_id from </span></span><br><span class="line"><span class="keyword">set</span> autotrace traceonly</span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="keyword">set</span> timing <span class="keyword">on</span></span><br><span class="line"><span class="keyword">select</span> object_id <span class="keyword">from</span> t <span class="keyword">where</span> object_id&lt;=<span class="number">5</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">--试验3：通过构造联合索引，再观察一个消除TABLE ACCESS BY INDEX ROWID的例子</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_un_objid_objname <span class="keyword">on</span> t(object_id,object_name);</span><br><span class="line"><span class="keyword">set</span> autotrace traceonly</span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="keyword">set</span> timing <span class="keyword">on</span></span><br><span class="line"><span class="keyword">select</span> object_id,object_name <span class="keyword">from</span> t <span class="keyword">where</span> object_id&lt;=<span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>结论：通过观察执行计划，我们发现当不回表的时候，性能更好，因为只在索引当中就查到了我们想要的字段。</p>
</blockquote>
<h4 id="索引优化之索引的高度"><a href="#索引优化之索引的高度" class="headerlink" title="索引优化之索引的高度"></a>索引优化之索引的高度</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t1 <span class="keyword">as</span> <span class="keyword">select</span> <span class="keyword">rownum</span> <span class="keyword">as</span> <span class="keyword">id</span> ,<span class="keyword">rownum</span>+<span class="number">1</span> <span class="keyword">as</span> id2,rpad(<span class="string">'*'</span>,<span class="number">1000</span>,<span class="string">'*'</span>) <span class="keyword">as</span> <span class="keyword">contents</span> <span class="keyword">from</span> dual <span class="keyword">connect</span> <span class="keyword">by</span> <span class="keyword">level</span>&lt;=<span class="number">2</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t2 <span class="keyword">as</span> <span class="keyword">select</span> <span class="keyword">rownum</span> <span class="keyword">as</span> <span class="keyword">id</span> ,<span class="keyword">rownum</span>+<span class="number">1</span> <span class="keyword">as</span> id2,rpad(<span class="string">'*'</span>,<span class="number">1000</span>,<span class="string">'*'</span>) <span class="keyword">as</span> <span class="keyword">contents</span> <span class="keyword">from</span> dual <span class="keyword">connect</span> <span class="keyword">by</span> <span class="keyword">level</span>&lt;=<span class="number">20</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t3 <span class="keyword">as</span> <span class="keyword">select</span> <span class="keyword">rownum</span> <span class="keyword">as</span> <span class="keyword">id</span> ,<span class="keyword">rownum</span>+<span class="number">1</span> <span class="keyword">as</span> id2,rpad(<span class="string">'*'</span>,<span class="number">1000</span>,<span class="string">'*'</span>) <span class="keyword">as</span> <span class="keyword">contents</span> <span class="keyword">from</span> dual <span class="keyword">connect</span> <span class="keyword">by</span> <span class="keyword">level</span>&lt;=<span class="number">200</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t4 <span class="keyword">as</span> <span class="keyword">select</span> <span class="keyword">rownum</span> <span class="keyword">as</span> <span class="keyword">id</span> ,<span class="keyword">rownum</span>+<span class="number">1</span> <span class="keyword">as</span> id2,rpad(<span class="string">'*'</span>,<span class="number">1000</span>,<span class="string">'*'</span>) <span class="keyword">as</span> <span class="keyword">contents</span> <span class="keyword">from</span> dual <span class="keyword">connect</span> <span class="keyword">by</span> <span class="keyword">level</span>&lt;=<span class="number">2000</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t5 <span class="keyword">as</span> <span class="keyword">select</span> <span class="keyword">rownum</span> <span class="keyword">as</span> <span class="keyword">id</span> ,<span class="keyword">rownum</span>+<span class="number">1</span> <span class="keyword">as</span> id2,rpad(<span class="string">'*'</span>,<span class="number">1000</span>,<span class="string">'*'</span>) <span class="keyword">as</span> <span class="keyword">contents</span> <span class="keyword">from</span> dual <span class="keyword">connect</span> <span class="keyword">by</span> <span class="keyword">level</span>&lt;=<span class="number">20000</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t6 <span class="keyword">as</span> <span class="keyword">select</span> <span class="keyword">rownum</span> <span class="keyword">as</span> <span class="keyword">id</span> ,<span class="keyword">rownum</span>+<span class="number">1</span> <span class="keyword">as</span> id2,rpad(<span class="string">'*'</span>,<span class="number">1000</span>,<span class="string">'*'</span>) <span class="keyword">as</span> <span class="keyword">contents</span> <span class="keyword">from</span> dual <span class="keyword">connect</span> <span class="keyword">by</span> <span class="keyword">level</span>&lt;=<span class="number">200000</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t7 <span class="keyword">as</span> <span class="keyword">select</span> <span class="keyword">rownum</span> <span class="keyword">as</span> <span class="keyword">id</span> ,<span class="keyword">rownum</span>+<span class="number">1</span> <span class="keyword">as</span> id2,rpad(<span class="string">'*'</span>,<span class="number">1000</span>,<span class="string">'*'</span>) <span class="keyword">as</span> <span class="keyword">contents</span> <span class="keyword">from</span> dual <span class="keyword">connect</span> <span class="keyword">by</span> <span class="keyword">level</span>&lt;=<span class="number">2000000</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_id_t1 <span class="keyword">on</span> t1(<span class="keyword">id</span>);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_id_t2 <span class="keyword">on</span> t2(<span class="keyword">id</span>);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_id_t3 <span class="keyword">on</span> t3(<span class="keyword">id</span>);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_id_t4 <span class="keyword">on</span> t4(<span class="keyword">id</span>);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_id_t5 <span class="keyword">on</span> t5(<span class="keyword">id</span>);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_id_t6 <span class="keyword">on</span> t6(<span class="keyword">id</span>);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_id_t7 <span class="keyword">on</span> t7(<span class="keyword">id</span>);</span><br><span class="line"><span class="comment">--观察索引的高度的不同</span></span><br><span class="line"><span class="keyword">select</span> index_name,<span class="comment">--索引名字</span></span><br><span class="line">          blevel,<span class="comment">--索引高度</span></span><br><span class="line">          leaf_blocks,<span class="comment">--索引叶子占用块</span></span><br><span class="line">          num_rows,<span class="comment">--行数</span></span><br><span class="line">          distinct_keys,</span><br><span class="line">          clustering_factor</span><br><span class="line">     <span class="keyword">from</span> user_ind_statistics</span><br><span class="line">    <span class="keyword">where</span> table_name <span class="keyword">in</span>( <span class="string">'T1'</span>,<span class="string">'T2'</span>,<span class="string">'T3'</span>,<span class="string">'T4'</span>,<span class="string">'T5'</span>,<span class="string">'T6'</span>,<span class="string">'T7'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">--执行    </span></span><br><span class="line"><span class="keyword">set</span> autotrace traceonly <span class="keyword">statistics</span></span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="comment">--注意观察逻辑读的次数，另外每条语句执行2遍以上，观察第2遍的结果。为了消除物理读以及递归调用。</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t1 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t2 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t3 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t4 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t5 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t6 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t7 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>;</span><br><span class="line"><span class="comment">--观察全表扫描的结果</span></span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+full(t1)*/</span> * <span class="keyword">from</span> t1 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>; </span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+full(t2)*/</span> * <span class="keyword">from</span> t2 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>; </span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+full(t3)*/</span> * <span class="keyword">from</span> t3 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>; </span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+full(t4)*/</span> * <span class="keyword">from</span> t4 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>; </span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+full(t5)*/</span> * <span class="keyword">from</span> t5 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>; </span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+full(t6)*/</span> * <span class="keyword">from</span> t6 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>; </span><br><span class="line"><span class="keyword">select</span> <span class="comment">/*+full(t7)*/</span> * <span class="keyword">from</span> t7 <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>结论：当索引的高度相同时，使用索引查询，发现产生逻辑读的次数一般相同；而使用全表扫描的差异巨大。但是请注意这是建立在返回一条数据或者少数数据的前体下，如果返回大量的数据，使用索引反而比使用全表扫描更慢。另外，在本案例中，大家也许觉得两百万数据量在全表扫描时执行速度也很快，那是因为这些表字段很少，oracle不会占用很多的数据块，当表的字段同样类型的增加到40个乃至更多，速度自然而然就慢了，因为遍历的块更多了。所以我们有时可以删除一些不需要的字段从而使某些需要全表扫描的场合性能提高。</p>
</blockquote>
<h4 id="索引优化之count，sum，avg"><a href="#索引优化之count，sum，avg" class="headerlink" title="索引优化之count，sum，avg"></a>索引优化之count，sum，avg</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> t <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="keyword">update</span> t <span class="keyword">set</span> object_id=<span class="keyword">rownum</span>;</span><br><span class="line"><span class="keyword">commit</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx1_object_id <span class="keyword">on</span> t(object_id);</span><br><span class="line"><span class="keyword">set</span> autotrace <span class="keyword">on</span></span><br><span class="line"><span class="comment">--此时走的是全表扫描</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> t;</span><br><span class="line"><span class="comment">--此时走的是索引</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> t <span class="keyword">where</span> object_id <span class="keyword">is</span> <span class="keyword">not</span> <span class="literal">null</span>;</span><br><span class="line"><span class="comment">--也可以不加is not null，直接把列的属性设置为not null，也成，继续试验如下：</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> t <span class="keyword">modify</span> OBJECT_ID <span class="keyword">not</span> <span class="literal">null</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> t;</span><br><span class="line"><span class="comment">--如果是主键就无需定义列是否允许为空了。因为主键本身就不能为空，此时也可走索引</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--sum以及avg同样的套路，可以自行实验，至少在11g当中是这样</span></span><br></pre></td></tr></table></figure>

<blockquote>
<p>结论：当我们在使用count函数，或者是sum以及avg聚合函数时，当此列有索引的时候，要注意此列的取值是否可为空，这关系到是否使用索引；当没有索引时，根据实际情况考虑是否添加索引。</p>
</blockquote>
<h4 id="索引优化之distinct"><a href="#索引优化之distinct" class="headerlink" title="索引优化之distinct"></a>索引优化之distinct</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> t <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="keyword">update</span> t <span class="keyword">set</span> object_id=<span class="keyword">rownum</span>;</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> T <span class="keyword">modify</span> OBJECT_ID <span class="keyword">not</span> <span class="literal">null</span>;</span><br><span class="line"><span class="keyword">update</span> t <span class="keyword">set</span> object_id=<span class="number">2</span>;</span><br><span class="line"><span class="keyword">update</span> t <span class="keyword">set</span> object_id=<span class="number">3</span> <span class="keyword">where</span> <span class="keyword">rownum</span>&lt;=<span class="number">25000</span>;</span><br><span class="line"><span class="keyword">commit</span>;</span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="keyword">set</span> autotrace traceonly</span><br><span class="line"><span class="comment">--测试1</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> object_id <span class="keyword">from</span> t ;</span><br><span class="line"><span class="comment">--测试2</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_t_object_id <span class="keyword">on</span> t(object_id);</span><br><span class="line"><span class="keyword">select</span>  <span class="comment">/*+index(t)*/</span> <span class="keyword">distinct</span> object_id <span class="keyword">from</span> t ;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>结论：经测试发现，执行计划当中并没有显示其发生了排序操作，那是因为在oracle10g的R2环境之后，DISTINCT由于其 HASH UNIQUE的算法导致其不会产生排序，不过虽然没有排序，通过观察执行计划当中的TempSpc可知distinct消耗PGA内存进行HASH UNIQUE运算；接下来看看建了索引后的情况，TempSpc关键字并没有消失，因此网上许多所说的会消失是不正确的，至少在oracle11g（11.2.0.1.0）当中并没有消失，但是COST确实下降许多；不过在现实中，DISTINCT往往靠索引来优化效果并不是很明显，因为大多数用到DISTINCT的地方是因为我们的表中有重复记录，因此，我们一般首先要考虑的是为什么会重复。</p>
</blockquote>
<h4 id="索引优化之union"><a href="#索引优化之union" class="headerlink" title="索引优化之union"></a>索引优化之union</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">----UNION 是需要排序的</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> t1 <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t1 <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects <span class="keyword">where</span> object_id <span class="keyword">is</span> <span class="keyword">not</span> <span class="literal">null</span>;</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> t1 <span class="keyword">modify</span> OBJECT_ID <span class="keyword">not</span> <span class="literal">null</span>;</span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> t2 <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t2 <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects <span class="keyword">where</span> object_id <span class="keyword">is</span> <span class="keyword">not</span> <span class="literal">null</span>;</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> t2 <span class="keyword">modify</span> OBJECT_ID <span class="keyword">not</span> <span class="literal">null</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="keyword">set</span> autotrace traceonly</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> object_id <span class="keyword">from</span> t1</span><br><span class="line"><span class="keyword">union</span></span><br><span class="line"><span class="keyword">select</span> object_id <span class="keyword">from</span> t2;</span><br><span class="line"></span><br><span class="line"><span class="comment">--发现索引无法消除UNION 排序</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_t1_object_id <span class="keyword">on</span> t1(object_id);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_t2_object_id <span class="keyword">on</span> t2(object_id);</span><br><span class="line"><span class="keyword">set</span> autotrace traceonly</span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span>  object_id <span class="keyword">from</span> t1</span><br><span class="line"><span class="keyword">union</span></span><br><span class="line"><span class="keyword">select</span>  object_id <span class="keyword">from</span> t2;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>结论：使用UNION是会产生排序的，使用索引无法消除UNION 排序，因此我们在使用的时候要确定是否有使用的 必要，如果表中没有重复记录，则使用UNION ALL即可。</p>
</blockquote>
<h4 id="索引优化之order-by"><a href="#索引优化之order-by" class="headerlink" title="索引优化之order by"></a>索引优化之order by</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> t <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects ;</span><br><span class="line"><span class="keyword">set</span> autotrace traceonly</span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="comment">--以下语句没有索引又有order by ，必然产生排序</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t <span class="keyword">where</span> object_id&gt;<span class="number">2</span> <span class="keyword">order</span> <span class="keyword">by</span> object_id;</span><br><span class="line"><span class="comment">---新增索引后，Oracle就有可能利用索引本身就有序的特点，利用索引来避免排序，如下：</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_t_object_id <span class="keyword">on</span> t(object_id);</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t <span class="keyword">where</span> object_id&gt;<span class="number">2</span> <span class="keyword">order</span> <span class="keyword">by</span> object_id;</span><br><span class="line"><span class="comment">--如下情况Oracle肯定毫不犹豫的选择用索引,因为回表取消了 !      </span></span><br><span class="line"><span class="keyword">select</span>  object_id <span class="keyword">from</span> t <span class="keyword">where</span> object_id&gt;<span class="number">2</span> <span class="keyword">order</span> <span class="keyword">by</span> object_id;</span><br><span class="line"><span class="comment">--如下情况，就算有索引，oracle也不会用索引</span></span><br><span class="line"><span class="keyword">select</span>  object_id <span class="keyword">from</span> t <span class="keyword">where</span> object_id&gt;<span class="number">2</span>;</span><br></pre></td></tr></table></figure>

<h3 id="索引的劣势"><a href="#索引的劣势" class="headerlink" title="索引的劣势"></a>索引的劣势</h3><h4 id="位图索引遭遇更新苦不堪言"><a href="#位图索引遭遇更新苦不堪言" class="headerlink" title="位图索引遭遇更新苦不堪言"></a>位图索引遭遇更新苦不堪言</h4><p>测试数据准备</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> t <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t </span><br><span class="line">(name_id,</span><br><span class="line"> gender <span class="keyword">not</span> <span class="literal">null</span>,</span><br><span class="line"> location <span class="keyword">not</span> <span class="literal">null</span>,</span><br><span class="line"> age_group <span class="keyword">not</span> <span class="literal">null</span>,</span><br><span class="line"> <span class="keyword">data</span></span><br><span class="line"> )</span><br><span class="line"> <span class="keyword">as</span></span><br><span class="line"> <span class="keyword">select</span> <span class="keyword">rownum</span>,</span><br><span class="line">        <span class="keyword">decode</span>(<span class="keyword">ceil</span>(dbms_random.value(<span class="number">0</span>,<span class="number">2</span>)),</span><br><span class="line">               <span class="number">1</span>,<span class="string">'M'</span>,</span><br><span class="line">               <span class="number">2</span>,<span class="string">'F'</span>)gender,</span><br><span class="line">        <span class="keyword">ceil</span>(dbms_random.value(<span class="number">1</span>,<span class="number">50</span>)) location,</span><br><span class="line">        <span class="keyword">decode</span>(<span class="keyword">ceil</span>(dbms_random.value(<span class="number">0</span>,<span class="number">3</span>)),</span><br><span class="line">               <span class="number">1</span>,<span class="string">'child'</span>,</span><br><span class="line">               <span class="number">2</span>,<span class="string">'young'</span>,</span><br><span class="line">               <span class="number">3</span>,<span class="string">'middle_age'</span>,</span><br><span class="line">               <span class="number">4</span>,<span class="string">'old'</span>),</span><br><span class="line">         rpad(<span class="string">'*'</span>,<span class="number">400</span>,<span class="string">'*'</span>)</span><br><span class="line"><span class="keyword">from</span> dual <span class="keyword">connect</span> <span class="keyword">by</span> <span class="keyword">rownum</span>&lt;=<span class="number">100000</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">bitmap</span> <span class="keyword">index</span> gender_idx <span class="keyword">on</span> t(gender);</span><br><span class="line"><span class="comment">---实验一：</span></span><br><span class="line"><span class="comment">--SESSION 1 不提交</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t(name_id,gender,location ,age_group ,<span class="keyword">data</span>) <span class="keyword">values</span> (<span class="number">100001</span>,<span class="string">'M'</span>,<span class="number">45</span>,<span class="string">'child'</span>,rpad(<span class="string">'*'</span>,<span class="number">20</span>,<span class="string">'*'</span>));</span><br><span class="line"><span class="comment">--SESSION 2</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t(name_id,gender,location ,age_group ,<span class="keyword">data</span>) <span class="keyword">values</span> (<span class="number">100002</span>,<span class="string">'M'</span>,<span class="number">46</span>, <span class="string">'young'</span>, rpad(<span class="string">'*'</span>,<span class="number">20</span>,<span class="string">'*'</span>));</span><br><span class="line"><span class="comment">---实验二：</span></span><br><span class="line"><span class="comment">--SESSION 1（持有者）</span></span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> T <span class="keyword">WHERE</span> GENDER=<span class="string">'M'</span> <span class="keyword">AND</span> LOCATION=<span class="number">25</span>;</span><br><span class="line"><span class="comment">---SESSION 2(其他会话) 插入带M的记录就立即被阻挡，以下三条语句都会被阻止</span></span><br><span class="line"><span class="keyword">insert</span>  <span class="keyword">into</span> t (name_id,gender,location ,age_group ,<span class="keyword">data</span>) <span class="keyword">values</span> (<span class="number">100001</span>,<span class="string">'M'</span>,<span class="number">78</span>, <span class="string">'young'</span>,<span class="string">'TTT'</span>);</span><br><span class="line"><span class="keyword">update</span> t <span class="keyword">set</span> gender=<span class="string">'M'</span> <span class="keyword">WHERE</span> LOCATION=<span class="number">25</span>;</span><br><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> T <span class="keyword">WHERE</span> GENDER=<span class="string">'M'</span>;</span><br><span class="line"><span class="comment">--实验三：</span></span><br><span class="line"><span class="comment">--SESSION 1（持有者）</span></span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> T <span class="keyword">WHERE</span> GENDER=<span class="string">'M'</span> <span class="keyword">AND</span> LOCATION=<span class="number">25</span>;</span><br><span class="line"><span class="comment">---SESSION 2(其他会话)</span></span><br><span class="line"><span class="comment">--以下是可以进行不受阻碍的</span></span><br><span class="line"><span class="keyword">insert</span>  <span class="keyword">into</span> t (name_id,gender,location ,age_group ,<span class="keyword">data</span>) <span class="keyword">values</span> (<span class="number">100001</span>,<span class="string">'F'</span>,<span class="number">78</span>, <span class="string">'young'</span>,<span class="string">'TTT'</span>);</span><br><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span>  t <span class="keyword">where</span> gender=<span class="string">'F'</span> ;</span><br><span class="line"><span class="keyword">UPDATE</span> T <span class="keyword">SET</span> LOCATION=<span class="number">100</span> <span class="keyword">WHERE</span> <span class="keyword">ROWID</span> <span class="keyword">NOT</span> <span class="keyword">IN</span> ( <span class="keyword">SELECT</span> <span class="keyword">ROWID</span> <span class="keyword">FROM</span> T <span class="keyword">WHERE</span> GENDER=<span class="string">'F'</span> <span class="keyword">AND</span> LOCATION=<span class="number">25</span>) ;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>结论：当你对于某一张表的一个字段加了位图索引之后，对于此表的操作，目前可以说几乎只能是单进程单用户操作了，这样的操作显然不支持高并发，对于整体系统并没有好处。因此要注意位图索引的使用场景。</p>
</blockquote>
<h4 id="建索引过程会产生全表锁"><a href="#建索引过程会产生全表锁" class="headerlink" title="建索引过程会产生全表锁"></a>建索引过程会产生全表锁</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> t <span class="keyword">purge</span>;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="comment">--尽可能插入多点数据，不然操作太快来不及发现</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t  <span class="keyword">select</span> * <span class="keyword">from</span> t;</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t  <span class="keyword">select</span> * <span class="keyword">from</span> t;</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t  <span class="keyword">select</span> * <span class="keyword">from</span> t;</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t  <span class="keyword">select</span> * <span class="keyword">from</span> t;</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t  <span class="keyword">select</span> * <span class="keyword">from</span> t;</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t  <span class="keyword">select</span> * <span class="keyword">from</span> t;</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t  <span class="keyword">select</span> * <span class="keyword">from</span> t;</span><br><span class="line"><span class="keyword">commit</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">--SESSION 1</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_object_id <span class="keyword">on</span> t(object_id);</span><br><span class="line"><span class="comment">--SESSION 2</span></span><br><span class="line"><span class="keyword">update</span> t <span class="keyword">set</span> object_id=<span class="number">99999</span> <span class="keyword">where</span> object_id=<span class="number">8</span>;</span><br><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> t <span class="keyword">where</span> object_id=<span class="number">8</span>;</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span>  t (OWNER) <span class="keyword">values</span> (<span class="string">'111'</span>);</span><br></pre></td></tr></table></figure>

<blockquote>
<p>结论：经上述实验可得，普通的对表建索引将会导致针对该表的更新操作无法进行，需要等待索引建完。更新操作将会被建索引动作阻塞。SESSION2 的三个操作，均需要SESSION1的索引建完之后才能执行。因此，在我们平时建索引的时候，尤其是对于一些大表在生产系统建立索引的时候一定要在夜里业务不繁忙的时候进行。</p>
</blockquote>
<h4 id="索引过多影响插入"><a href="#索引过多影响插入" class="headerlink" title="索引过多影响插入"></a>索引过多影响插入</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> test1 <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> test2 <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> test3 <span class="keyword">as</span> <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_owner <span class="keyword">on</span> test1(owner);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_object_name <span class="keyword">on</span> test1(object_name);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_data_obj_id <span class="keyword">on</span> test1(data_object_id);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_created <span class="keyword">on</span> test1(created);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_last_ddl_time <span class="keyword">on</span> test1(last_ddl_time);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_status <span class="keyword">on</span> test1(<span class="keyword">status</span>);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_t2_sta <span class="keyword">on</span> test2(<span class="keyword">status</span>);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_t2_objid <span class="keyword">on</span> test2(object_id);</span><br><span class="line"><span class="keyword">set</span> timing <span class="keyword">on</span> </span><br><span class="line"><span class="comment">--语句1(test1表有6个索引）</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> test1 <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="keyword">commit</span>;</span><br><span class="line"><span class="comment">--语句2(test2表有2个索引）</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> test2 <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="keyword">commit</span>;</span><br><span class="line"><span class="comment">--语句3(test3表有无索引）</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> test3 <span class="keyword">select</span> * <span class="keyword">from</span> dba_objects;</span><br><span class="line"><span class="keyword">commit</span>;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>结论：索引越多，插入速度越慢。因为有了索引，更新了记录就更新了索引，就要维护索引那种有序排列的结构，开销比较大。因此我们在项目中建索引要考虑全面，并且建索引要规范。而我们在项目中若遇到大批量的向大表中插入数据时，我们可以先禁用索引，插入完成之后再重建索引，这样效率会比有索引插入快。因为此时重建索引是一个批量的动作，而不是单次插入单次维护索引。</p>
</blockquote>
<h2 id="表连接优化"><a href="#表连接优化" class="headerlink" title="表连接优化"></a>表连接优化</h2><h4 id="NL连接（Nest-Loop）"><a href="#NL连接（Nest-Loop）" class="headerlink" title="NL连接（Nest Loop）"></a>NL连接（Nest Loop）</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> t1 <span class="keyword">CASCADE</span> <span class="keyword">CONSTRAINTS</span> <span class="keyword">PURGE</span>; </span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> t2 <span class="keyword">CASCADE</span> <span class="keyword">CONSTRAINTS</span> <span class="keyword">PURGE</span>; </span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> t1 (</span><br><span class="line">     <span class="keyword">id</span> <span class="built_in">NUMBER</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">     n <span class="built_in">NUMBER</span>,</span><br><span class="line">     <span class="keyword">contents</span> <span class="built_in">VARCHAR2</span>(<span class="number">4000</span>)); </span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> t2 (</span><br><span class="line">     <span class="keyword">id</span> <span class="built_in">NUMBER</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">     t1_id <span class="built_in">NUMBER</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">     n <span class="built_in">NUMBER</span>,</span><br><span class="line">     <span class="keyword">contents</span> <span class="built_in">VARCHAR2</span>(<span class="number">4000</span>)); </span><br><span class="line"></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> t1 <span class="keyword">SELECT</span> <span class="keyword">rownum</span>,<span class="keyword">rownum</span>, dbms_random.string(<span class="string">'a'</span>, <span class="number">50</span>) <span class="keyword">FROM</span> dual  <span class="keyword">CONNECT</span> <span class="keyword">BY</span> <span class="keyword">level</span> &lt;= <span class="number">10000</span> <span class="keyword">ORDER</span> <span class="keyword">BY</span> dbms_random.random; </span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> t2 <span class="keyword">SELECT</span> <span class="keyword">rownum</span>, <span class="keyword">rownum</span>, <span class="keyword">rownum</span>, dbms_random.string(<span class="string">'b'</span>, <span class="number">50</span>) <span class="keyword">FROM</span> dual <span class="keyword">CONNECT</span> <span class="keyword">BY</span> <span class="keyword">level</span> &lt;= <span class="number">1000000</span> <span class="keyword">ORDER</span> <span class="keyword">BY</span> dbms_random.random; </span><br><span class="line"><span class="keyword">COMMIT</span>; </span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t1) use_nl(t2) */</span> * <span class="keyword">FROM</span> t1,t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id <span class="keyword">AND</span> t1.n= <span class="number">19</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="comment">--收集统计信息.</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">session</span> <span class="keyword">set</span> statistics_level=<span class="keyword">all</span> ;</span><br><span class="line"></span><br><span class="line"><span class="comment">---1.对t1表的限制条件建索引</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">INDEX</span> t1_n <span class="keyword">ON</span> t1 (n);</span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t1) use_nl(t2) */</span> * <span class="keyword">FROM</span> t1,t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id <span class="keyword">AND</span> t1.n= <span class="number">19</span></span><br><span class="line"><span class="comment">--查看执行计划</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">table</span>(dbms_xplan.display_cursor(<span class="literal">null</span>,<span class="literal">null</span>,<span class="string">'allstats last'</span>));</span><br><span class="line"></span><br><span class="line"><span class="comment">---2.在1的基础上对被驱动表t2表的连接条件建索引</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">INDEX</span> t2_t1_id <span class="keyword">ON</span> t2(t1_id);</span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t1) use_nl(t2) */</span> * <span class="keyword">FROM</span> t1,t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id <span class="keyword">AND</span> t1.n= <span class="number">19</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">table</span>(dbms_xplan.display_cursor(<span class="literal">null</span>,<span class="literal">null</span>,<span class="string">'allstats last'</span>));</span><br><span class="line"></span><br><span class="line"><span class="comment">---3.确保小结果集先驱动</span></span><br><span class="line"><span class="comment">--我们构造如下：假如oracle的统计信息不准确</span></span><br><span class="line"><span class="comment">--以下故意错乱颠倒，欺骗Oracle说T1表是大表而T2表是小表。</span></span><br><span class="line">EXEC  dbms_stats.SET_table_stats(user, 'T1', numrows =&gt; 20000000  ,numblks =&gt; 1000000);</span><br><span class="line">EXEC  dbms_stats.SET_table_stats(user, 'T2', numrows =&gt; 1  ,numblks =&gt; 1);</span><br><span class="line"><span class="comment">--此时t2表为驱动表，t1表为被驱动表，此时发现性能大幅度下降</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id  t2.t1_id <span class="keyword">AND</span> t1.n &lt;= <span class="number">19</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">table</span>(dbms_xplan.display_cursor(<span class="literal">null</span>,<span class="literal">null</span>,<span class="string">'allstats last'</span>));</span><br></pre></td></tr></table></figure>

<blockquote>
<p>结论：通过以上实验的执行计划可以看出，当使用NL连接的时候，我们保证以上三点，会使sql达到比较好的性能。</p>
<p>为什么在嵌套循环的驱动表的限制条件建索引,被驱动表的连接条件建索引？<br>驱动表的限制条件建索引的目的是为了减少扫描驱动表的时间。如果在驱动表的连接条件建索引就没有任何意义，所有列关联到另一表的所有列，等同于每条记录都要进行关联，而在驱动表的限制条件建了索引之后，只快速返回一条或者几条记录，然后在传递给被驱动表的连接条件列，一般情况下被驱动表对应的驱动表的记录返回的不多，所以在被驱动表的连接条件建立索引是有必要的。</p>
</blockquote>
<h4 id="HASH-JOIN连接"><a href="#HASH-JOIN连接" class="headerlink" title="HASH JOIN连接"></a>HASH JOIN连接</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--环境构造</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> t1 <span class="keyword">CASCADE</span> <span class="keyword">CONSTRAINTS</span> <span class="keyword">PURGE</span>; </span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> t2 <span class="keyword">CASCADE</span> <span class="keyword">CONSTRAINTS</span> <span class="keyword">PURGE</span>; </span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> t1 (</span><br><span class="line">     <span class="keyword">id</span> <span class="built_in">NUMBER</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">     n <span class="built_in">NUMBER</span>,</span><br><span class="line">     <span class="keyword">contents</span> <span class="built_in">VARCHAR2</span>(<span class="number">4000</span>)</span><br><span class="line">   ); </span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> t2 (</span><br><span class="line">     <span class="keyword">id</span> <span class="built_in">NUMBER</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">     t1_id <span class="built_in">NUMBER</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">     n <span class="built_in">NUMBER</span>,</span><br><span class="line">     <span class="keyword">contents</span> <span class="built_in">VARCHAR2</span>(<span class="number">4000</span>)</span><br><span class="line">   );  </span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> t1 <span class="keyword">SELECT</span>  <span class="keyword">rownum</span>,  <span class="keyword">rownum</span>, dbms_random.string(<span class="string">'a'</span>, <span class="number">50</span>) <span class="keyword">FROM</span> dual </span><br><span class="line"><span class="keyword">CONNECT</span> <span class="keyword">BY</span> <span class="keyword">level</span> &lt;= <span class="number">10000</span></span><br><span class="line">      <span class="keyword">ORDER</span> <span class="keyword">BY</span> dbms_random.random; </span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> t2 <span class="keyword">SELECT</span> <span class="keyword">rownum</span>, <span class="keyword">rownum</span>, <span class="keyword">rownum</span>, dbms_random.string(<span class="string">'b'</span>, <span class="number">50</span>) <span class="keyword">FROM</span> dual <span class="keyword">CONNECT</span> <span class="keyword">BY</span> <span class="keyword">level</span> &lt;= <span class="number">100000</span></span><br><span class="line">    <span class="keyword">ORDER</span> <span class="keyword">BY</span> dbms_random.random; </span><br><span class="line"><span class="keyword">COMMIT</span>; </span><br><span class="line"></span><br><span class="line"><span class="comment">--1.两表的限制条件有索引(注：针对索引条件返回记录很少的情况）！</span></span><br><span class="line"><span class="comment">--首先测试Hash Join两表的限制条件皆无索引的情况</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">session</span> <span class="keyword">set</span> statistics_level=<span class="keyword">all</span> ;</span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t2) use_hash(t1)*/</span> * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id <span class="keyword">and</span> t1.n=<span class="number">19</span> <span class="keyword">and</span> t2.n=<span class="number">12</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">table</span>(dbms_xplan.display_cursor(<span class="literal">null</span>,<span class="literal">null</span>,<span class="string">'allstats last'</span>));</span><br><span class="line"><span class="comment">--在t1表的限制条件建索引</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_t1_n <span class="keyword">on</span> t1(n);</span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t2) use_hash(t1)*/</span> * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id <span class="keyword">and</span> t1.n=<span class="number">19</span> <span class="keyword">and</span> t2.n=<span class="number">12</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">table</span>(dbms_xplan.display_cursor(<span class="literal">null</span>,<span class="literal">null</span>,<span class="string">'allstats last'</span>));</span><br><span class="line"><span class="comment">--在t2表的限制条件建索引</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_t2_n <span class="keyword">on</span> t2(n);   </span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t2) use_hash(t1)*/</span> * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id <span class="keyword">and</span> t1.n=<span class="number">19</span> <span class="keyword">and</span> t2.n=<span class="number">12</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">table</span>(dbms_xplan.display_cursor(<span class="literal">null</span>,<span class="literal">null</span>,<span class="string">'allstats last'</span>));</span><br><span class="line"><span class="comment">--2.确保小结果集先驱动</span></span><br><span class="line"><span class="comment">--t1为驱动表</span></span><br><span class="line"><span class="keyword">SELECT</span>  * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">table</span>(dbms_xplan.display_cursor(<span class="literal">null</span>,<span class="literal">null</span>,<span class="string">'allstats last'</span>));</span><br><span class="line"><span class="comment">--t2为驱动表</span></span><br><span class="line"><span class="keyword">SELECT</span>  <span class="comment">/*+ leading(t2) use_hash(t1)*/</span> * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">table</span>(dbms_xplan.display_cursor(<span class="literal">null</span>,<span class="literal">null</span>,<span class="string">'allstats last'</span>));</span><br><span class="line"><span class="comment">--结论：在2的实验中发现，虽然buffers的变化可能很小甚至一样，但是userd-mem却差异比较明显。说明排序尺寸差异明显。在结合时间发现，两者的时间差异也比较明显。大表为驱动表所耗时间较长。</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--3.确保PGA尺寸足够</span></span><br><span class="line"><span class="comment">--因为HASH JOIN连接会使用到PGA内存区域的hash area，若内存不够，会用到磁盘的空间。</span></span><br></pre></td></tr></table></figure>

<h4 id="Merge-Sort-Join连接"><a href="#Merge-Sort-Join连接" class="headerlink" title="Merge Sort Join连接"></a>Merge Sort Join连接</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--环境构造同上hash join</span></span><br><span class="line"><span class="comment">--1.两表的限制条件有索引</span></span><br><span class="line"><span class="comment">--两表限制条件皆无索引的情况</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">session</span> <span class="keyword">set</span> statistics_level=<span class="keyword">all</span> ;</span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t2) use_merge(t1)*/</span> * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id <span class="keyword">and</span> t1.n=<span class="number">19</span> <span class="keyword">and</span> t2.n=<span class="number">12</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">table</span>(dbms_xplan.display_cursor(<span class="literal">null</span>,<span class="literal">null</span>,<span class="string">'allstats last'</span>));</span><br><span class="line"><span class="comment">--t1表的限制条件建索引</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_t1_n <span class="keyword">on</span> t1(n);</span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t2) use_merge(t1)*/</span> * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id <span class="keyword">and</span> t1.n=<span class="number">19</span> <span class="keyword">and</span> t2.n=<span class="number">12</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">table</span>(dbms_xplan.display_cursor(<span class="literal">null</span>,<span class="literal">null</span>,<span class="string">'allstats last'</span>));</span><br><span class="line"><span class="comment">--t2表的限制条件建索引</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_t2_n <span class="keyword">on</span> t2(n);</span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t2) use_merge(t1)*/</span> * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id <span class="keyword">and</span> t1.n=<span class="number">19</span> <span class="keyword">and</span> t2.n=<span class="number">12</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">table</span>(dbms_xplan.display_cursor(<span class="literal">null</span>,<span class="literal">null</span>,<span class="string">'allstats last'</span>));</span><br><span class="line"><span class="comment">--2.连接字段有索引，争取利用索引来消除排序（可惜的是，在11g以及之前版本，ORACLE算法的限制，只能避免一次排序，后续版本未知，需要试验）</span></span><br><span class="line"><span class="comment">--两表的连接条件都无索引的情况，有两次排序：</span></span><br><span class="line"><span class="keyword">set</span> linesize <span class="number">1000</span></span><br><span class="line"><span class="keyword">set</span> autotrace traceonly</span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t1) use_merge(t2)*/</span> * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id;</span><br><span class="line"><span class="comment">--t1表建索引，发现排序消除了一个</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">INDEX</span> idx_t1_id <span class="keyword">ON</span> t1(<span class="keyword">id</span>); </span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t1) use_merge(t2)*/</span> * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id;</span><br><span class="line"><span class="comment">--在t2表的连接条件建索引，发现排序依然有一个，无法消除</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">INDEX</span> idx_t2_t1_id <span class="keyword">ON</span> t2(t1_id);   </span><br><span class="line"><span class="keyword">SELECT</span> <span class="comment">/*+ leading(t1) use_merge(t2)*/</span> * <span class="keyword">FROM</span> t1, t2 <span class="keyword">WHERE</span> t1.id = t2.t1_id;</span><br><span class="line"><span class="comment">--3.避免多余列致排序尺寸过大（取部分字段）</span></span><br><span class="line"><span class="comment">--对于NL以及hash连接来说，只取部分列性能基本不会有什么变化</span></span><br><span class="line"><span class="comment">--4.确保PGA尺寸足够</span></span><br></pre></td></tr></table></figure>

      
    </div>
    
    
    

    

    

    
      <div>
        <ul class="post-copyright">
  <li class="post-copyright-author">
    <strong>本文作者：</strong>
    ysshao
  </li>
  <li class="post-copyright-link">
    <strong>本文链接：</strong>
    <a href="https://ysshao.cn/Oracle/SQL/Oracle_SQL_optimization/" title="SQL性能优化案例">https://ysshao.cn/Oracle/SQL/Oracle_SQL_optimization/</a>
  </li>
  <li class="post-copyright-license">
    <strong>版权声明： </strong>
    本博客所有文章遵循 <a href="https://creativecommons.org/licenses/by-nc-sa/3.0/" rel="external nofollow" target="_blank">BY-NC-SA</a> 许可协议。转载请注明出处！
  </li>
</ul>

      </div>
    
<div>
  
    <div>
    
        <div style="text-align:center;color: #ccc;font-size:14px;">本文结束<i class="fa fa-paw"></i>感谢您的阅读</div>
    
</div>
  
</div>
    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/Oracle/" rel="tag"><i class="fa fa-tag"></i> Oracle</a>
          
            <a href="/tags/SQL%E4%BC%98%E5%8C%96/" rel="tag"><i class="fa fa-tag"></i> SQL优化</a>
          
        </div>
      

      
      
        <div class="post-widgets">
        

        

        
          
          <div id="needsharebutton-postbottom">
            <span class="btn">
              <i class="fa fa-share-alt" aria-hidden="true"></i>
            </span>
          </div>
        
        </div>
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/Oracle/Oracle_Tran/" rel="next" title="Oracle事务&锁">
                <i class="fa fa-chevron-left"></i> Oracle事务&锁
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/Linux/Linux_performance/" rel="prev" title="Linux性能分析工具合集">
                Linux性能分析工具合集 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          


          

        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <img class="site-author-image" itemprop="image"
                src="/images/allen.jpg"
                alt="ysshao" />
            
              <p class="site-author-name" itemprop="name">ysshao</p>
              <p class="site-description motion-element" itemprop="description"><font size="2" >本站主要涉及日常技术分享<br>欢迎各位指正和交流。</font><br/></p>
          </div>

          <nav class="site-state motion-element">

            
              <div class="site-state-item site-state-posts">
              
                <a href="/archives/%7C%7C%20archive">
              
                  <span class="site-state-item-count">5</span>
                  <span class="site-state-item-name">日志</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-categories">
                <a href="/categories/index.html">
                  <span class="site-state-item-count">4</span>
                  <span class="site-state-item-name">分类</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-tags">
                <a href="/tags/index.html">
                  <span class="site-state-item-count">5</span>
                  <span class="site-state-item-name">标签</span>
                </a>
              </div>
            

          </nav>

          
            <div class="feed-link motion-element">
              <a href="/atom.xml" rel="alternate">
                <i class="fa fa-rss"></i>
                RSS
              </a>
            </div>
          

          
            <div class="links-of-author motion-element">
                
                  <span class="links-of-author-item">
                    <a href="https://github.com/allenshao9" target="_blank" title="GitHub">
                      
                        <i class="fa fa-fw fa-github-alt"></i>GitHub</a>
                  </span>
                
                  <span class="links-of-author-item">
                    <a href="https://ysshao.cn/Notes/" target="_blank" title="Notes">
                      
                        <i class="fa fa-fw fa-gg"></i>Notes</a>
                  </span>
                
            </div>
          
          
          


          
          

          
          

          

        </div>
      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#SQL性能优化案例篇"><span class="nav-number">1.</span> <span class="nav-text">SQL性能优化案例篇</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#学会辨别虚假信息"><span class="nav-number">1.1.</span> <span class="nav-text">学会辨别虚假信息</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#in与exist之争"><span class="nav-number">1.1.1.</span> <span class="nav-text">in与exist之争</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#count谁最快"><span class="nav-number">1.1.2.</span> <span class="nav-text">count谁最快</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#表的条件顺序"><span class="nav-number">1.1.3.</span> <span class="nav-text">表的条件顺序</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#表的连接顺序"><span class="nav-number">1.1.4.</span> <span class="nav-text">表的连接顺序</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#索引优化的优劣"><span class="nav-number">1.2.</span> <span class="nav-text">索引优化的优劣</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#索引的优势"><span class="nav-number">1.2.1.</span> <span class="nav-text">索引的优势</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#索引优化之回表"><span class="nav-number">1.2.1.1.</span> <span class="nav-text">索引优化之回表</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#索引优化之索引的高度"><span class="nav-number">1.2.1.2.</span> <span class="nav-text">索引优化之索引的高度</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#索引优化之count，sum，avg"><span class="nav-number">1.2.1.3.</span> <span class="nav-text">索引优化之count，sum，avg</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#索引优化之distinct"><span class="nav-number">1.2.1.4.</span> <span class="nav-text">索引优化之distinct</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#索引优化之union"><span class="nav-number">1.2.1.5.</span> <span class="nav-text">索引优化之union</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#索引优化之order-by"><span class="nav-number">1.2.1.6.</span> <span class="nav-text">索引优化之order by</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#索引的劣势"><span class="nav-number">1.2.2.</span> <span class="nav-text">索引的劣势</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#位图索引遭遇更新苦不堪言"><span class="nav-number">1.2.2.1.</span> <span class="nav-text">位图索引遭遇更新苦不堪言</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#建索引过程会产生全表锁"><span class="nav-number">1.2.2.2.</span> <span class="nav-text">建索引过程会产生全表锁</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#索引过多影响插入"><span class="nav-number">1.2.2.3.</span> <span class="nav-text">索引过多影响插入</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#表连接优化"><span class="nav-number">1.3.</span> <span class="nav-text">表连接优化</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#NL连接（Nest-Loop）"><span class="nav-number">1.3.0.1.</span> <span class="nav-text">NL连接（Nest Loop）</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#HASH-JOIN连接"><span class="nav-number">1.3.0.2.</span> <span class="nav-text">HASH JOIN连接</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#Merge-Sort-Join连接"><span class="nav-number">1.3.0.3.</span> <span class="nav-text">Merge Sort Join连接</span></a></li></ol></li></ol></li></ol></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      
      </br>
		<div style="">
  <canvas id="canvas" style="width:60%;">当前浏览器不支持canvas，请更换浏览器后再试</canvas>
</div>
<script>
(function(){

   var digit=
    [
        [
            [0,0,1,1,1,0,0],
            [0,1,1,0,1,1,0],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [0,1,1,0,1,1,0],
            [0,0,1,1,1,0,0]
        ],//0
        [
            [0,0,0,1,1,0,0],
            [0,1,1,1,1,0,0],
            [0,0,0,1,1,0,0],
            [0,0,0,1,1,0,0],
            [0,0,0,1,1,0,0],
            [0,0,0,1,1,0,0],
            [0,0,0,1,1,0,0],
            [0,0,0,1,1,0,0],
            [0,0,0,1,1,0,0],
            [1,1,1,1,1,1,1]
        ],//1
        [
            [0,1,1,1,1,1,0],
            [1,1,0,0,0,1,1],
            [0,0,0,0,0,1,1],
            [0,0,0,0,1,1,0],
            [0,0,0,1,1,0,0],
            [0,0,1,1,0,0,0],
            [0,1,1,0,0,0,0],
            [1,1,0,0,0,0,0],
            [1,1,0,0,0,1,1],
            [1,1,1,1,1,1,1]
        ],//2
        [
            [1,1,1,1,1,1,1],
            [0,0,0,0,0,1,1],
            [0,0,0,0,1,1,0],
            [0,0,0,1,1,0,0],
            [0,0,1,1,1,0,0],
            [0,0,0,0,1,1,0],
            [0,0,0,0,0,1,1],
            [0,0,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [0,1,1,1,1,1,0]
        ],//3
        [
            [0,0,0,0,1,1,0],
            [0,0,0,1,1,1,0],
            [0,0,1,1,1,1,0],
            [0,1,1,0,1,1,0],
            [1,1,0,0,1,1,0],
            [1,1,1,1,1,1,1],
            [0,0,0,0,1,1,0],
            [0,0,0,0,1,1,0],
            [0,0,0,0,1,1,0],
            [0,0,0,1,1,1,1]
        ],//4
        [
            [1,1,1,1,1,1,1],
            [1,1,0,0,0,0,0],
            [1,1,0,0,0,0,0],
            [1,1,1,1,1,1,0],
            [0,0,0,0,0,1,1],
            [0,0,0,0,0,1,1],
            [0,0,0,0,0,1,1],
            [0,0,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [0,1,1,1,1,1,0]
        ],//5
        [
            [0,0,0,0,1,1,0],
            [0,0,1,1,0,0,0],
            [0,1,1,0,0,0,0],
            [1,1,0,0,0,0,0],
            [1,1,0,1,1,1,0],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [0,1,1,1,1,1,0]
        ],//6
        [
            [1,1,1,1,1,1,1],
            [1,1,0,0,0,1,1],
            [0,0,0,0,1,1,0],
            [0,0,0,0,1,1,0],
            [0,0,0,1,1,0,0],
            [0,0,0,1,1,0,0],
            [0,0,1,1,0,0,0],
            [0,0,1,1,0,0,0],
            [0,0,1,1,0,0,0],
            [0,0,1,1,0,0,0]
        ],//7
        [
            [0,1,1,1,1,1,0],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [0,1,1,1,1,1,0],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [0,1,1,1,1,1,0]
        ],//8
        [
            [0,1,1,1,1,1,0],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [1,1,0,0,0,1,1],
            [0,1,1,1,0,1,1],
            [0,0,0,0,0,1,1],
            [0,0,0,0,0,1,1],
            [0,0,0,0,1,1,0],
            [0,0,0,1,1,0,0],
            [0,1,1,0,0,0,0]
        ],//9
        [
            [0,0,0,0,0,0,0],
            [0,0,1,1,1,0,0],
            [0,0,1,1,1,0,0],
            [0,0,1,1,1,0,0],
            [0,0,0,0,0,0,0],
            [0,0,0,0,0,0,0],
            [0,0,1,1,1,0,0],
            [0,0,1,1,1,0,0],
            [0,0,1,1,1,0,0],
            [0,0,0,0,0,0,0]
        ]//:
    ];

var canvas = document.getElementById('canvas');

if(canvas.getContext){
    var cxt = canvas.getContext('2d');
    //声明canvas的宽高
    var H = 100,W = 700;
    canvas.height = H;
    canvas.width = W;
    cxt.fillStyle = '#f00';
    cxt.fillRect(10,10,50,50);

    //存储时间数据
    var data = [];
    //存储运动的小球
    var balls = [];
    //设置粒子半径
    var R = canvas.height/20-1;
    (function(){
        var temp = /(\d)(\d):(\d)(\d):(\d)(\d)/.exec(new Date());
        //存储时间数字，由十位小时、个位小时、冒号、十位分钟、个位分钟、冒号、十位秒钟、个位秒钟这7个数字组成
        data.push(temp[1],temp[2],10,temp[3],temp[4],10,temp[5],temp[6]);
    })();

    /*生成点阵数字*/
    function renderDigit(index,num){
        for(var i = 0; i < digit[num].length; i++){
            for(var j = 0; j < digit[num][i].length; j++){
                if(digit[num][i][j] == 1){
                    cxt.beginPath();
                    cxt.arc(14*(R+2)*index + j*2*(R+1)+(R+1),i*2*(R+1)+(R+1),R,0,2*Math.PI);
                    cxt.closePath();
                    cxt.fill();
                }
            }
        }
    }

    /*更新时钟*/
    function updateDigitTime(){
        var changeNumArray = [];
        var temp = /(\d)(\d):(\d)(\d):(\d)(\d)/.exec(new Date());
        var NewData = [];
        NewData.push(temp[1],temp[2],10,temp[3],temp[4],10,temp[5],temp[6]);
        for(var i = data.length-1; i >=0 ; i--){
            //时间发生变化
            if(NewData[i] !== data[i]){
                //将变化的数字值和在data数组中的索引存储在changeNumArray数组中
                changeNumArray.push(i+'_'+(Number(data[i])+1)%10);
            }
        }
        //增加小球
        for(var i = 0; i< changeNumArray.length; i++){
            addBalls.apply(this,changeNumArray[i].split('_'));
        }
        data = NewData.concat();
    }

    /*更新小球状态*/
    function updateBalls(){
        for(var i = 0; i < balls.length; i++){
            balls[i].stepY += balls[i].disY;
            balls[i].x += balls[i].stepX;
            balls[i].y += balls[i].stepY;
            if(balls[i].x > W + R || balls[i].y > H + R){
                balls.splice(i,1);
                i--;
            }
        }
    }

    /*增加要运动的小球*/
    function addBalls(index,num){
        var numArray = [1,2,3];
        var colorArray =  ["#3BE","#09C","#A6C","#93C","#9C0","#690","#FB3","#F80","#F44","#C00"];
        for(var i = 0; i < digit[num].length; i++){
            for(var j = 0; j < digit[num][i].length; j++){
                if(digit[num][i][j] == 1){
                    var ball = {
                        x:14*(R+2)*index + j*2*(R+1)+(R+1),
                        y:i*2*(R+1)+(R+1),
                        stepX:Math.floor(Math.random() * 4 -2),
                        stepY:-2*numArray[Math.floor(Math.random()*numArray.length)],
                        color:colorArray[Math.floor(Math.random()*colorArray.length)],
                        disY:1
                    };
                    balls.push(ball);
                }
            }
        }
    }

    /*渲染*/
    function render(){
        //重置画布宽度，达到清空画布的效果
        canvas.height = 100;
        //渲染时钟
        for(var i = 0; i < data.length; i++){
            renderDigit(i,data[i]);
        }
        //渲染小球
        for(var i = 0; i < balls.length; i++){
            cxt.beginPath();
            cxt.arc(balls[i].x,balls[i].y,R,0,2*Math.PI);
            cxt.fillStyle = balls[i].color;
            cxt.closePath();
            cxt.fill();
        }
    }

    clearInterval(oTimer);
    var oTimer = setInterval(function(){
        //更新时钟
        updateDigitTime();
        //更新小球状态
        updateBalls();
        //渲染
        render();
    },50);
}

})();
</script>
    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; <span itemprop="copyrightYear">2020</span>
  <span class="with-love">
   &nbsp; <i class="fa fa-user"></i>&nbsp;
  </span>
  <span class="author" itemprop="copyrightHolder"> ysshao</span>

  
</div>


  <div class="powered-by">  热爱生活 </div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">分享技术 </div>



<br/>
        
        <span id="busuanzi_container_site_pv">
                本站总访问量<span id="busuanzi_value_site_pv"></span> 次
        </span>
        <span class="post-meta-divider">|</span>
        <span id="busuanzi_container_site_uv" style='display:none'>
                本站访客数<span id="busuanzi_value_site_uv"></span>人
        </span>
        <script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>

        







        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
          <span id="scrollpercent"><span>0</span>%</span>
        
      </div>
    

    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  




  
  









  
  
    <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>
  

  
  
    <script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>
  

  
  
    <script type="text/javascript" src="/lib/three/three.min.js"></script>
  

  
  
    <script type="text/javascript" src="/lib/three/three-waves.min.js"></script>
  


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.1.4"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.1.4"></script>



  
  


  <script type="text/javascript" src="/js/src/affix.js?v=5.1.4"></script>

  <script type="text/javascript" src="/js/src/schemes/pisces.js?v=5.1.4"></script>



  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.4"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=5.1.4"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.4"></script>



  


  

    
      <script id="dsq-count-scr" src="https://.disqus.com/count.js" async></script>
    

    

  




	





  





  












  

  <script type="text/javascript">
    // Popup Window;
    var isfetched = false;
    var isXml = true;
    // Search DB path;
    var search_path = "search.xml";
    if (search_path.length === 0) {
      search_path = "search.xml";
    } else if (/json$/i.test(search_path)) {
      isXml = false;
    }
    var path = "/" + search_path;
    // monitor main search box;

    var onPopupClose = function (e) {
      $('.popup').hide();
      $('#local-search-input').val('');
      $('.search-result-list').remove();
      $('#no-result').remove();
      $(".local-search-pop-overlay").remove();
      $('body').css('overflow', '');
    }

    function proceedsearch() {
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay"></div>')
        .css('overflow', 'hidden');
      $('.search-popup-overlay').click(onPopupClose);
      $('.popup').toggle();
      var $localSearchInput = $('#local-search-input');
      $localSearchInput.attr("autocapitalize", "none");
      $localSearchInput.attr("autocorrect", "off");
      $localSearchInput.focus();
    }

    // search function;
    var searchFunc = function(path, search_id, content_id) {
      'use strict';

      // start loading animation
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay">' +
          '<div id="search-loading-icon">' +
          '<i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>' +
          '</div>' +
          '</div>')
        .css('overflow', 'hidden');
      $("#search-loading-icon").css('margin', '20% auto 0 auto').css('text-align', 'center');

      $.ajax({
        url: path,
        dataType: isXml ? "xml" : "json",
        async: true,
        success: function(res) {
          // get the contents from search data
          isfetched = true;
          $('.popup').detach().appendTo('.header-inner');
          var datas = isXml ? $("entry", res).map(function() {
            return {
              title: $("title", this).text(),
              content: $("content",this).text(),
              url: $("url" , this).text()
            };
          }).get() : res;
          var input = document.getElementById(search_id);
          var resultContent = document.getElementById(content_id);
          var inputEventFunction = function() {
            var searchText = input.value.trim().toLowerCase();
            var keywords = searchText.split(/[\s\-]+/);
            if (keywords.length > 1) {
              keywords.push(searchText);
            }
            var resultItems = [];
            if (searchText.length > 0) {
              // perform local searching
              datas.forEach(function(data) {
                var isMatch = false;
                var hitCount = 0;
                var searchTextCount = 0;
                var title = data.title.trim();
                var titleInLowerCase = title.toLowerCase();
                var content = data.content.trim().replace(/<[^>]+>/g,"");
                var contentInLowerCase = content.toLowerCase();
                var articleUrl = decodeURIComponent(data.url);
                var indexOfTitle = [];
                var indexOfContent = [];
                // only match articles with not empty titles
                if(title != '') {
                  keywords.forEach(function(keyword) {
                    function getIndexByWord(word, text, caseSensitive) {
                      var wordLen = word.length;
                      if (wordLen === 0) {
                        return [];
                      }
                      var startPosition = 0, position = [], index = [];
                      if (!caseSensitive) {
                        text = text.toLowerCase();
                        word = word.toLowerCase();
                      }
                      while ((position = text.indexOf(word, startPosition)) > -1) {
                        index.push({position: position, word: word});
                        startPosition = position + wordLen;
                      }
                      return index;
                    }

                    indexOfTitle = indexOfTitle.concat(getIndexByWord(keyword, titleInLowerCase, false));
                    indexOfContent = indexOfContent.concat(getIndexByWord(keyword, contentInLowerCase, false));
                  });
                  if (indexOfTitle.length > 0 || indexOfContent.length > 0) {
                    isMatch = true;
                    hitCount = indexOfTitle.length + indexOfContent.length;
                  }
                }

                // show search results

                if (isMatch) {
                  // sort index by position of keyword

                  [indexOfTitle, indexOfContent].forEach(function (index) {
                    index.sort(function (itemLeft, itemRight) {
                      if (itemRight.position !== itemLeft.position) {
                        return itemRight.position - itemLeft.position;
                      } else {
                        return itemLeft.word.length - itemRight.word.length;
                      }
                    });
                  });

                  // merge hits into slices

                  function mergeIntoSlice(text, start, end, index) {
                    var item = index[index.length - 1];
                    var position = item.position;
                    var word = item.word;
                    var hits = [];
                    var searchTextCountInSlice = 0;
                    while (position + word.length <= end && index.length != 0) {
                      if (word === searchText) {
                        searchTextCountInSlice++;
                      }
                      hits.push({position: position, length: word.length});
                      var wordEnd = position + word.length;

                      // move to next position of hit

                      index.pop();
                      while (index.length != 0) {
                        item = index[index.length - 1];
                        position = item.position;
                        word = item.word;
                        if (wordEnd > position) {
                          index.pop();
                        } else {
                          break;
                        }
                      }
                    }
                    searchTextCount += searchTextCountInSlice;
                    return {
                      hits: hits,
                      start: start,
                      end: end,
                      searchTextCount: searchTextCountInSlice
                    };
                  }

                  var slicesOfTitle = [];
                  if (indexOfTitle.length != 0) {
                    slicesOfTitle.push(mergeIntoSlice(title, 0, title.length, indexOfTitle));
                  }

                  var slicesOfContent = [];
                  while (indexOfContent.length != 0) {
                    var item = indexOfContent[indexOfContent.length - 1];
                    var position = item.position;
                    var word = item.word;
                    // cut out 100 characters
                    var start = position - 20;
                    var end = position + 80;
                    if(start < 0){
                      start = 0;
                    }
                    if (end < position + word.length) {
                      end = position + word.length;
                    }
                    if(end > content.length){
                      end = content.length;
                    }
                    slicesOfContent.push(mergeIntoSlice(content, start, end, indexOfContent));
                  }

                  // sort slices in content by search text's count and hits' count

                  slicesOfContent.sort(function (sliceLeft, sliceRight) {
                    if (sliceLeft.searchTextCount !== sliceRight.searchTextCount) {
                      return sliceRight.searchTextCount - sliceLeft.searchTextCount;
                    } else if (sliceLeft.hits.length !== sliceRight.hits.length) {
                      return sliceRight.hits.length - sliceLeft.hits.length;
                    } else {
                      return sliceLeft.start - sliceRight.start;
                    }
                  });

                  // select top N slices in content

                  var upperBound = parseInt('-1');
                  if (upperBound >= 0) {
                    slicesOfContent = slicesOfContent.slice(0, upperBound);
                  }

                  // highlight title and content

                  function highlightKeyword(text, slice) {
                    var result = '';
                    var prevEnd = slice.start;
                    slice.hits.forEach(function (hit) {
                      result += text.substring(prevEnd, hit.position);
                      var end = hit.position + hit.length;
                      result += '<b class="search-keyword">' + text.substring(hit.position, end) + '</b>';
                      prevEnd = end;
                    });
                    result += text.substring(prevEnd, slice.end);
                    return result;
                  }

                  var resultItem = '';

                  if (slicesOfTitle.length != 0) {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</a>";
                  } else {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + title + "</a>";
                  }

                  slicesOfContent.forEach(function (slice) {
                    resultItem += "<a href='" + articleUrl + "'>" +
                      "<p class=\"search-result\">" + highlightKeyword(content, slice) +
                      "...</p>" + "</a>";
                  });

                  resultItem += "</li>";
                  resultItems.push({
                    item: resultItem,
                    searchTextCount: searchTextCount,
                    hitCount: hitCount,
                    id: resultItems.length
                  });
                }
              })
            };
            if (keywords.length === 1 && keywords[0] === "") {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-search fa-5x" /></div>'
            } else if (resultItems.length === 0) {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>'
            } else {
              resultItems.sort(function (resultLeft, resultRight) {
                if (resultLeft.searchTextCount !== resultRight.searchTextCount) {
                  return resultRight.searchTextCount - resultLeft.searchTextCount;
                } else if (resultLeft.hitCount !== resultRight.hitCount) {
                  return resultRight.hitCount - resultLeft.hitCount;
                } else {
                  return resultRight.id - resultLeft.id;
                }
              });
              var searchResultList = '<ul class=\"search-result-list\">';
              resultItems.forEach(function (result) {
                searchResultList += result.item;
              })
              searchResultList += "</ul>";
              resultContent.innerHTML = searchResultList;
            }
          }

          if ('auto' === 'auto') {
            input.addEventListener('input', inputEventFunction);
          } else {
            $('.search-icon').click(inputEventFunction);
            input.addEventListener('keypress', function (event) {
              if (event.keyCode === 13) {
                inputEventFunction();
              }
            });
          }

          // remove loading animation
          $(".local-search-pop-overlay").remove();
          $('body').css('overflow', '');

          proceedsearch();
        }
      });
    }

    // handle and trigger popup window;
    $('.popup-trigger').click(function(e) {
      e.stopPropagation();
      if (isfetched === false) {
        searchFunc(path, 'local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };
    });

    $('.popup-btn-close').click(onPopupClose);
    $('.popup').click(function(e){
      e.stopPropagation();
    });
    $(document).on('keyup', function (event) {
      var shouldDismissSearchPopup = event.which === 27 &&
        $('.search-popup').is(':visible');
      if (shouldDismissSearchPopup) {
        onPopupClose();
      }
    });
  </script>





  

  
  <script src="https://cdn1.lncld.net/static/js/av-core-mini-0.6.4.js"></script>
  <script>AV.initialize("URBmXUQ1icUgpq16QemUL2AC-gzGzoHsz", "Yon3WfLkAnaC3KuHxJlWt8NL");</script>
  <script>
    function showTime(Counter) {
      var query = new AV.Query(Counter);
      var entries = [];
      var $visitors = $(".leancloud_visitors");

      $visitors.each(function () {
        entries.push( $(this).attr("id").trim() );
      });

      query.containedIn('url', entries);
      query.find()
        .done(function (results) {
          var COUNT_CONTAINER_REF = '.leancloud-visitors-count';

          if (results.length === 0) {
            $visitors.find(COUNT_CONTAINER_REF).text(0);
            return;
          }

          for (var i = 0; i < results.length; i++) {
            var item = results[i];
            var url = item.get('url');
            var time = item.get('time');
            var element = document.getElementById(url);

            $(element).find(COUNT_CONTAINER_REF).text(time);
          }
          for(var i = 0; i < entries.length; i++) {
            var url = entries[i];
            var element = document.getElementById(url);
            var countSpan = $(element).find(COUNT_CONTAINER_REF);
            if( countSpan.text() == '') {
              countSpan.text(0);
            }
          }
        })
        .fail(function (object, error) {
          console.log("Error: " + error.code + " " + error.message);
        });
    }

    function addCount(Counter) {
      var $visitors = $(".leancloud_visitors");
      var url = $visitors.attr('id').trim();
      var title = $visitors.attr('data-flag-title').trim();
      var query = new AV.Query(Counter);

      query.equalTo("url", url);
      query.find({
        success: function(results) {
          if (results.length > 0) {
            var counter = results[0];
            counter.fetchWhenSave(true);
            counter.increment("time");
            counter.save(null, {
              success: function(counter) {
                var $element = $(document.getElementById(url));
                $element.find('.leancloud-visitors-count').text(counter.get('time'));
              },
              error: function(counter, error) {
                console.log('Failed to save Visitor num, with error message: ' + error.message);
              }
            });
          } else {
            var newcounter = new Counter();
            /* Set ACL */
            var acl = new AV.ACL();
            acl.setPublicReadAccess(true);
            acl.setPublicWriteAccess(true);
            newcounter.setACL(acl);
            /* End Set ACL */
            newcounter.set("title", title);
            newcounter.set("url", url);
            newcounter.set("time", 1);
            newcounter.save(null, {
              success: function(newcounter) {
                var $element = $(document.getElementById(url));
                $element.find('.leancloud-visitors-count').text(newcounter.get('time'));
              },
              error: function(newcounter, error) {
                console.log('Failed to create');
              }
            });
          }
        },
        error: function(error) {
          console.log('Error:' + error.code + " " + error.message);
        }
      });
    }

    $(function() {
      var Counter = AV.Object.extend("Counter");
      if ($('.leancloud_visitors').length == 1) {
        addCount(Counter);
      } else if ($('.post-title-link').length > 1) {
        showTime(Counter);
      }
    });
  </script>



  

  
<script>
(function(){
    var bp = document.createElement('script');
    var curProtocol = window.location.protocol.split(':')[0];
    if (curProtocol === 'https') {
        bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';        
    }
    else {
        bp.src = 'http://push.zhanzhang.baidu.com/push.js';
    }
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(bp, s);
})();
</script>


  
  
  
  <link rel="stylesheet" href="/lib/needsharebutton/needsharebutton.css">

  
  
  <script src="/lib/needsharebutton/needsharebutton.js"></script>

  <script>
    
      pbOptions = {};
      
          pbOptions.iconStyle = "default";
      
          pbOptions.boxForm = "horizontal";
      
          pbOptions.position = "bottomCenter";
      
          pbOptions.networks = "Weibo,Wechat,Douban,QQZone,Twitter,Facebook";
      
      new needShareButton('#needsharebutton-postbottom', pbOptions);
    
    
  </script>

  

  

  

  

</body>
</html>
